package com.viknix.jdbc.insert;

import cn.hutool.core.util.RandomUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.math.BigDecimal;
import java.sql.*;
import java.util.Objects;

/**
 * @Author: Dongqi
 * @Date: 2021/11/4 14:34
 * @Version 1.0
 * @Description:
 */
public class BatchInsert {

    long start = 0;

    @Before
    public void init() {
        start = System.currentTimeMillis();
    }

    @Test
    public void jdbcBatchInsert() throws SQLException {
        // 1核2G：插入一万条耗时：1422 ms
        // 1核2G：插入十万条耗时：6712 ms
        // 1核2G：插入五十万条耗时：30965 ms
        // 1核2G：插入一百万条耗时：57960 ms
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            // 注意：1、必须带上rewriteBatchedStatements=true参数
            // 2、在带上参数之后，批处理的sql语句末尾不能带有分号，会报异常
            // 3、rewriteBatchedStatements=true 对 DML 语句能显著提升执行速度，但是对于 DQL 语句，需要使用流式读取或者游标读取进行优化
            conn = DriverManager.getConnection("jdbc:mysql://101.34.50.82:3306/mysql-demo?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true", "root", "123456");
            conn.setAutoCommit(false);
            String sql = "INSERT INTO wh_b_inventory(inventory_id,sku_id,warehouse_id,warehouse_area_id,warehouse_location_id,warehouse_location_code,purpose,batch," +
                    "status,inbound_temp_store,warehouse_entry_date,initial_inventory,total_inventory,occupy_inventory,transit_inventory,freeze_inventory,basic_units_id," +
                    "version,receiving_units_id,conversion_ratio,delete_flag,created_by,created_by_name,created_time,last_upd_by,last_upd_by_name,last_upd_time,tenant_id) " +
                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < 1000000; i++) {
                ps.setLong(1, RandomUtil.randomInt(100000000, 999999999));
                ps.setLong(2, RandomUtil.randomInt(100000000, 999999999));
                ps.setLong(3, RandomUtil.randomInt(100000000, 999999999));
                ps.setLong(4, RandomUtil.randomInt(100000000, 999999999));
                ps.setLong(5, RandomUtil.randomInt(100000000, 999999999));
                ps.setString(6, RandomUtil.randomString(8));
                ps.setLong(7, RandomUtil.randomInt(100000000, 999999999));
                ps.setString(8, RandomUtil.randomString(8));
                ps.setInt(9, 1);
                ps.setInt(10, 1);
                ps.setDate(11, new Date(System.currentTimeMillis()));
                ps.setBigDecimal(12, new BigDecimal(RandomUtil.randomInt(10000, 99999) + ""));
                ps.setBigDecimal(13, new BigDecimal(RandomUtil.randomInt(10000, 99999) + ""));
                ps.setBigDecimal(14, new BigDecimal(RandomUtil.randomInt(10000, 99999) + ""));
                ps.setBigDecimal(15, new BigDecimal(RandomUtil.randomInt(10000, 99999) + ""));
                ps.setBigDecimal(16, new BigDecimal(RandomUtil.randomInt(10000, 99999) + ""));
                ps.setLong(17, RandomUtil.randomInt(100000000, 999999999));
                ps.setInt(18, RandomUtil.randomInt(1, 20));
                ps.setLong(19, RandomUtil.randomInt(100000000, 999999999));
                ps.setBigDecimal(20, new BigDecimal(RandomUtil.randomInt(10000, 99999) + ""));
                ps.setInt(21, 0);
                ps.setLong(22, RandomUtil.randomInt(100000000, 999999999));
                ps.setString(23, RandomUtil.randomString(8));
                ps.setDate(24, new Date(System.currentTimeMillis()));
                ps.setLong(25, RandomUtil.randomInt(100000000, 999999999));
                ps.setString(26, RandomUtil.randomString(8));
                ps.setDate(27, new Date(System.currentTimeMillis()));
                ps.setLong(28, RandomUtil.randomInt(100000000, 999999999));
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (Objects.nonNull(ps)) {
                ps.close();
            }
            if (Objects.nonNull(conn)) {
                conn.close();
            }
        }
    }

    @After
    public void after() {
        System.out.println("耗时：" + ((System.currentTimeMillis() - start)));
    }
}
